﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Data;

namespace SimpleUnitity.Database
{
    public delegate T InstanceHandle<T>(IDataRecord reader);

    public delegate void ExecuteEventHandler(DbTool sender, IDbCommand command);

    public delegate void ExecuteResultEventHandler(DbTool sender, object result);

    public delegate void ErrorEventHandle(DbTool sender, Exception exception);

    internal delegate T ExecuteHandle<T>();

    public abstract class DbTool : IDisposable
    {
        private IDbConnection connection;
        private IDbDataAdapter dataAdapter;
        private IDbTransaction transaction;

        private bool autoClose = true;

        public virtual string ParameterPrefix
        {
            get { return "@"; }
        }

        public virtual string StatementPrefix
        {
            get { return ""; }
        }

        public virtual string StatementSuffix
        {
            get { return ""; }
        }

        public string StatementSymbolAlias { get; set; }

        public DbTool(IDbConnection connection, IDbDataAdapter adapter, IDbTransaction transaction)
        {
            this.connection = connection;
            this.dataAdapter = adapter;
            this.transaction = transaction;
        }

        public DbTool(DbTool dbtool)
        {
            this.connection = dbtool.connection;
            this.dataAdapter = dbtool.dataAdapter;
            this.transaction = dbtool.transaction;
            this.StatementSymbolAlias = dbtool.StatementSymbolAlias;
        }

        #region 事务控制
        public virtual bool ExistTransaction
        {
            get { return transaction != null; }
        }

        public virtual void BeginTransaction()
        {
            if (transaction != null)
            {
                throw new TransactionException("Transaction was opened");
            }
            else
            {
                this.OpenConnection();
                transaction = this.connection.BeginTransaction();
            }
        }

        public virtual void Rollback()
        {
            if (this.transaction != null)
            {
                this.transaction.Rollback();
                this.transaction = null;
            }
            this.CloseWithoutTransaction();
        }

        public virtual void Commit()
        {
            if (this.transaction == null)
            {
                throw new TransactionException("Transaction was not opened");
            }
            else
            {
                this.transaction.Commit();
                this.transaction = null;
            }
            this.CloseWithoutTransaction();
        } 
        #endregion

        #region 连接管理
        public IDbConnection Connection
        {
            get { return connection; }
        }

        public virtual void Open()
        {
            autoClose = false;
            this.OpenConnection();
        }

        public virtual void Close()
        {
            autoClose = true;
            if (transaction != null)
            {
                transaction.Dispose();
                transaction = null;
            }
            this.CloseWithoutTransaction();
        }

        protected virtual void OpenConnection()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            else if (connection.State == ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
        }

        protected virtual void CloseWithoutTransaction()
        {
            if (this.transaction == null && autoClose == true)
            {
                this.connection.Close();
            }
        } 
        #endregion

        #region Handler Helpers
        private static object[] ArrayHandle(IDataRecord record)
        {
            var values = new object[record.FieldCount];
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = record.IsDBNull(i) ? null : record.GetValue(i);
            }
            return values;
        }

        private static IEqualityComparer<string> ignoreCaseStringComparer = StringComparer.Create(System.Globalization.CultureInfo.CurrentCulture, true);
        private static IDictionary<string, object> MapHandle(IDataRecord record)
        {
            IDictionary<string, object> map = new Dictionary<string, object>(ignoreCaseStringComparer);
            for (int i = 0; i < record.FieldCount; i++)
            {
                var name = record.GetName(i);
                var value = record.IsDBNull(i) ? null : record.GetValue(i);
                map.Add(name, value);
            }
            return map;
        }
        #endregion

        #region Events
        public event ExecuteEventHandler BeforeExecute;

        public event ErrorEventHandle OnError;

        public event ExecuteResultEventHandler AfterExecute;

        private void CallBeforeExecute(IDbCommand command)
        {
            if (BeforeExecute != null)
            {
                BeforeExecute(this, command);
            }
        }

        private void CallOnError(Exception exception)
        {
            if (OnError != null)
            {
                OnError(this, exception);
            }
        }

        private void CallAfterExecute(object result)
        {
            if (AfterExecute != null)
            {
                AfterExecute(this, result);
            }
        }
        #endregion

        #region Command
        protected virtual IDbCommand PretreatCommand(IDbCommand command)
        {
            command.Connection = connection;
            command.Transaction = transaction;
            this.OpenConnection();   // Open Connection
            // Call
            CallBeforeExecute(command);
            return command;
        }

        static readonly string[] RegexMetaChars = "$,\\,?,*,^,.,+,|,{,[,(,)".Split(',');
        public virtual string PretreatSql(string sql)
        {
            if (string.IsNullOrEmpty(StatementSymbolAlias))
            {
                return sql;
            }
            string alias = StatementSymbolAlias;
            if (Array.IndexOf(RegexMetaChars, alias) >= 0)
            {
                alias = "\\" + alias;
            }
            string partten = string.Format("{0}(.*?){0}", alias);
            while (Regex.IsMatch(sql, partten))
            {
                sql = Regex.Replace(sql, partten, new MatchEvaluator((match) =>
                {
                    return StatementPrefix + match.Groups[1].Value + StatementSuffix;
                }), RegexOptions.Compiled);
            }
            return sql;
        }

        protected virtual IDbCommand PretreatCommandByMap(string sql, CommandType commandType, IDictionary<string, object> args)
        {
            // 处理SQL
            sql = PretreatSql(sql);
            var command = connection.CreateCommand();
            command.CommandText = sql;
            command.CommandType = commandType;
            if (args != null)
            {
                foreach (var item in args)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = string.Format("{0}{1}", ParameterPrefix, item.Key);  //insert into user(name,pwd) values(@name, @pwd);
                    parameter.Value = item.Value ?? DBNull.Value;
                    command.Parameters.Add(parameter);
                }
            }
            return command;
            //return PretreatCommand(command);
        }

        protected virtual IDbCommand PretreatCommandByArray(string sql, CommandType commandType, object[] args)
        {
            sql = PretreatSql(sql);
            var command = connection.CreateCommand();
            command.CommandText = sql;
            command.CommandType = commandType;
            if (args != null)
            {
                for (int i = 0; i < args.Length; i++)
                {
                    if (args[i] is IDataParameter)
                    {
                        command.Parameters.Add(args[i] as IDataParameter);
                    }
                    else
                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = string.Format("{0}{1}", ParameterPrefix, i);  //insert into user(name,pwd) values(@0, @1);
                        parameter.Value = args[i] ?? DBNull.Value;
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
            //return PretreatCommand(command);
        } 
        #endregion

        #region Execute Command
        private T Execute<T>(ExecuteHandle<T> execHandle)
        {
            try
            {
                T result = execHandle();
                CallAfterExecute(result);
                return result;
            }
            catch (Exception ex)
            {
                CallOnError(ex);
                throw ex;
            }
            finally
            {
                this.CloseWithoutTransaction();
            }
        }

        public int ExecuteNonQuery(IDbCommand command)
        {
            return Execute(() => PretreatCommand(command).ExecuteNonQuery());
        }


        public object ExecuteScalar(IDbCommand command)
        {
            return Execute(() => PretreatCommand(command).ExecuteScalar());
        }

        public T ExecuteQueryForObject<T>(IDbCommand command, InstanceHandle<T> handle)
        {
            return Execute(() =>
            {
                T obj = default(T);
                using (var reader = PretreatCommand(command).ExecuteReader())
                {
                    if (reader.Read())
                    {
                        obj = handle(reader);
                    }
                }
                return obj;
            });
        }

        public IList<T> ExecuteQueryForList<T>(IDbCommand command, InstanceHandle<T> handle)
        {
            return Execute(() =>
            {
                IList<T> results = new List<T>();
                using (var reader = PretreatCommand(command).ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = handle(reader);
                        results.Add(item);
                    }
                }
                return results;
            });
        }

        public IList<object[]> ExecuteQueryForArray(IDbCommand command)
        {
            return ExecuteQueryForList<object[]>(command, ArrayHandle);
        }

        public IList<IDictionary<string, object>> ExecuteQueryForMap(IDbCommand command)
        {
            return ExecuteQueryForList<IDictionary<string, object>>(command, MapHandle);
        }

        #endregion

        #region Execute Command By Map

        public int ExecuteNonQuery(string sql, IDictionary<string, object> args)
        {
            return ExecuteNonQuery(PretreatCommandByMap(sql, CommandType.Text, args));
        }

        public object ExecuteScalar(string sql, IDictionary<string, object> args)
        {
            return ExecuteScalar(PretreatCommandByMap(sql, CommandType.Text, args));
        }

        public T ExecuteQueryForObject<T>(string sql, InstanceHandle<T> handle, IDictionary<string, object> args)
        {
            return ExecuteQueryForObject(PretreatCommandByMap(sql, CommandType.Text, args), handle);
        }

        public IList<T> ExecuteQueryForList<T>(string sql, InstanceHandle<T> handle, IDictionary<string, object> args)
        {
            return ExecuteQueryForList(PretreatCommandByMap(sql, CommandType.Text, args), handle);
        }

        public IList<object[]> ExecuteQueryForArray(string sql, IDictionary<string, object> args)
        {
            return ExecuteQueryForList(PretreatCommandByMap(sql, CommandType.Text, args), ArrayHandle);
        }

        public IList<IDictionary<string, object>> ExecuteQueryForMap(string sql, IDictionary<string, object> args)
        {
            return ExecuteQueryForList(PretreatCommandByMap(sql, CommandType.Text, args), MapHandle);
        }

        #endregion

        #region Execute Command By Array

        public int ExecuteNonQuery(string sql, params object[] args)
        {
            return ExecuteNonQuery(PretreatCommandByArray(sql, CommandType.Text, args));
        }

        public object ExecuteScalar(string sql, params object[] args)
        {
            return ExecuteScalar(PretreatCommandByArray(sql, CommandType.Text, args));
        }

        public T ExecuteQueryForObject<T>(string sql, InstanceHandle<T> handle, params object[] args)
        {
            return ExecuteQueryForObject(PretreatCommandByArray(sql, CommandType.Text, args), handle);
        }

        public IList<T> ExecuteQueryForList<T>(string sql, InstanceHandle<T> handle, params object[] args)
        {
            return ExecuteQueryForList(PretreatCommandByArray(sql, CommandType.Text, args), handle);
        }

        public IList<object[]> ExecuteQueryForArray(string sql, params object[] args)
        {
            return ExecuteQueryForList<object[]>(sql, ArrayHandle, args);
        }

        public IList<IDictionary<string, object>> ExecuteQueryForMap(string sql, params object[] args)
        {
            return ExecuteQueryForList<IDictionary<string, object>>(sql, MapHandle, args);
        }

        
        #endregion

        #region Execute DataSet
        public virtual DataSet ExecuteQueryForDataSet(IDbCommand command)
        {
            try
            {
                DataSet dataset = new DataSet();
                dataAdapter.SelectCommand = PretreatCommand(command);
                dataAdapter.Fill(dataset);
                return dataset;
            }
            catch (Exception ex)
            {
                CallOnError(ex);
                throw;
            }
            finally
            {
                this.Close();
            }
        }

        public virtual DataSet ExecuteQueryForDataSet(string sql, params object[] args)
        {
            return ExecuteQueryForDataSet(PretreatCommandByArray(sql, CommandType.Text, args));
        }

        public virtual DataSet ExecuteQueryForDataSet(string sql, IDictionary<string, object> args)
        {
            return ExecuteQueryForDataSet(PretreatCommandByMap(sql, CommandType.Text, args));
        }
        #endregion

        #region Execute DataTable

        public virtual DataTable ExecuteQueryForTable(IDbCommand command)
        {
            DataSet ds = ExecuteQueryForDataSet(command);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        public virtual DataTable ExecuteQueryForTable(string sql, params object[] args)
        {
            DataSet ds = ExecuteQueryForDataSet(sql, args);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        public virtual DataTable ExecuteQueryForTable(string sql, IDictionary<string, object> args)
        {
            DataSet ds = ExecuteQueryForDataSet(sql, args);
            return ds.Tables.Count > 0 ? ds.Tables[0] : null;
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            try
            {
                if (this.transaction != null)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    finally
                    {
                        transaction.Dispose();
                    }
                }
            }
            finally
            {
                if (this.connection != null)
                {
                    try
                    {
                        this.Close();
                    }
                    finally
                    {
                        this.connection.Dispose();
                    }
                }
            }
        }

        #endregion
    }
}
